/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Consultant;

import Address.AddressBean;
import Country.CountryBean;
import State.StateBean;
import java.sql.*;
import java.util.Vector;

/**
 *
 * @author mishra
 */
public class Query {

    static Connection con = null;

    public static int add(AddressBean ab, ConsultantBean cb) throws SQLException {
        int i = 0, k = 0, j = 0;

        try {
            j = Address.Query.add(ab);
            con = DBConnection.Connection.getConnection();

            PreparedStatement pre = con.prepareStatement("insert into consultanat values(consultantid.nextval,?,?,?,?,sysdate,?)");
            pre.setString(1, cb.getConsultantName());
            pre.setString(2, cb.getEmail());
            pre.setString(3, cb.getMobile());
            pre.setInt(4, j);
            pre.setString(5, cb.getEntryEmail());
            i = pre.executeUpdate();
            if (i != 0) {
                k = getMaxId();
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }

    public static int getMaxId() throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select max(CONSULTANAT_ID) from consultanat");
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = rs.getInt(1);
            }

        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static Vector viewAll() throws SQLException {
        Vector<Object> list = new Vector<Object>();
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from consultanat left join address on address.address_id=consultanat.address_id left join state on address.state_id=state.state_id left join country on address.country_id=country.country_id");
            ResultSet rs = pre.executeQuery();
            while (rs.next()) {
                ConsultantBean cb = new ConsultantBean();
                AddressBean ab = new AddressBean();
                StateBean sb = new StateBean();
                CountryBean cob = new CountryBean();
                cb.setConsultantID(rs.getInt(1));
                cb.setConsultantName(rs.getString(2));
                cb.setEmail(rs.getString(3));
                cb.setMobile(rs.getString(4));
                cb.setAddressId(rs.getInt(5));
                cb.setEntryDate(rs.getDate(6));
                cb.setEntryEmail(rs.getString(7));
                ab.setAddressId(rs.getInt(8));
                ab.setAddress1(rs.getString(9));
                ab.setAddress2(rs.getString(10));
                ab.setCountryId(rs.getInt(11));
                ab.setStateId(rs.getInt(12));
                ab.setPinCode(rs.getString(13));
                ab.setEntryDate(rs.getDate(14));
                sb.setStateId(rs.getInt(15));
                sb.setCountryId(rs.getInt(16));
                sb.setStateName(rs.getString(17));
                sb.setEntryDate(rs.getDate(18));
                ab.setStateBean(sb);
                cob.setCountryId(rs.getInt(19));
                cob.setCountryName(rs.getString(20));
                cob.setEntryDate(rs.getDate(21));
                ab.setCountryBean(cob);
                cb.setAddressBean(ab);
                list.add(cb);
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return list;

    }

    public static int delete(int id) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("delete from consultanat where consultanat_id=?");
            pre.setInt(1, id);
            i = pre.executeUpdate();
        } catch (Exception e) {
            return i;
        } finally {
            con.close();
        }
        return i;
    }

    public static int edit(AddressBean ab, ConsultantBean cb) throws SQLException {
        int i = 0, k = 0;

        try {
            con = DBConnection.Connection.getConnection();

            PreparedStatement pre = con.prepareStatement("update consultanat set CONSULTANAT_NAME=?,CONSULTANAT_EMAIL=?,CONSULTANAT_MOBILE=?, ENTRY_DATE=sysdate where CONSULTANAT_ID=?");
            pre.setString(1, cb.getConsultantName());
            pre.setString(2, cb.getEmail());
            pre.setString(3, cb.getMobile());
            pre.setInt(4, cb.getConsultantID());
            i = pre.executeUpdate();
            if (i != 0) {
                PreparedStatement pre1 = con.prepareStatement("update address set ADDRESS1=?,ADDRESS2=?,COUNTRY_ID=?,STATE_ID=?, PINCODE=?, ENTRYDATE=sysdate where ADDRESS_ID=?");
                pre1.setString(1, ab.getAddress1());
                pre1.setString(2, ab.getAddress2());
                pre1.setInt(3, ab.getCountryId());
                pre1.setInt(4, ab.getStateId());
                pre1.setString(5, ab.getPinCode());
                pre1.setInt(6, ab.getAddressId());
                k = pre1.executeUpdate();
            }

        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return k;
    }
}
